﻿#region Header
// ------------------------ Licence / Copyright ------------------------
// 
// ScrumTable for Scrum Meeting Support
// Copyright © HSR - Hochschule für Technik Rapperswil
// All Rights Reserved
// 
// Author:
//  Michael Gfeller Silvan Gehrig Patrick Boos
// 
// ---------------------------------------------------------------------
#endregion

#region Usings

using System;
using System.Data;
using System.Linq;
using ClosedXML.Excel;
using ScrumTable.Common.Data;
using ScrumTable.Common.Excel.OpenXmlSdk.Extensions;

#endregion

namespace ScrumTable.Common.Excel.OpenXmlSdk
{
    /// <summary>
    /// Provides the facilities for de-/serialization of an open xml sdk file.
    /// </summary>
    internal class WorkbookSerializer
    {
        #region Declarations
        //--------------------------------------------------------------------
        // Declarations
        //--------------------------------------------------------------------

        private readonly string _idColumnName;

        private DataSet _changes;
        private XLWorkbook _wbContext;

        #endregion

        #region Properties
        //--------------------------------------------------------------------
        // Properties
        //--------------------------------------------------------------------

        #endregion

        #region Constructors / Destructor
        //--------------------------------------------------------------------
        // Constructors / Destructor
        //--------------------------------------------------------------------

        internal WorkbookSerializer(string idColumnName)
        {
            PreCondition.AssertNotNullOrEmpty(idColumnName, "idColumnName");

            _idColumnName = idColumnName;
        }

        #endregion

        #region Methods
        //--------------------------------------------------------------------
        // Methods
        //--------------------------------------------------------------------

        /// <summary>
        /// Loads the data of the underlying open xml file.
        /// </summary>
        internal void SaveData(string moniker, DataSet toSerialize)
        {
            PreCondition.AssertNotNullOrEmpty(moniker, "moniker");
            PreCondition.AssertNotNull(toSerialize, "toSerialize");

            using (_wbContext = new XLWorkbook(moniker))
            {
                _changes = toSerialize.GetChanges();

                if (_changes != null)
                {
                    foreach (DataTable table in _changes.Tables)
                    {
                        SaveDataTable(table);
                    }
                }
                _wbContext.Save();
            } 
        }

        private void SaveDataTable(DataTable toSave)
        {
            IXLWorksheet sheet;

            if (_wbContext.Worksheets.TryGetWorksheet(toSave.TableName, out sheet))
            {
                foreach (DataRow row in toSave.Rows)
                {
                    SaveModifiedRow(sheet, row);
                }
            }
        }

        private void SaveModifiedRow(IXLWorksheet sheet, DataRow source)
        {
            IXLRow referencedRow = RetrieveTarget(sheet, source);
            
            if (referencedRow != null)
            {
                SaveRow(source, referencedRow); 
            }
            else
            {
                SaveRow(source, sheet.GetNextEmptyRow());
            }
        }

        private void SaveRow(DataRow source, IXLRow target)
        {
            var dataColumns = from dataColumn in source.Table.GetColumns()
                              where dataColumn.GetCellRef() != null
                              select dataColumn;

            dataColumns.ForEach(dataColumn =>
                                    {
                                        DataValue toWrite = (DataValue) source[dataColumn];
                                        IXLCell cell = target.Cell(dataColumn.GetCellRef().ColumnName);

                                        if (!cell.IsCalculated())
                                        {
                                            cell.Value = toWrite.Value;
                                            cell.Hyperlink = (toWrite.Link != null) ? new XLHyperlink(toWrite.Link) : null;
                                        }
                                    });
        }

        private IXLRow RetrieveTarget(IXLWorksheet sheet, DataRow source)
        {
            // first try: retrieve row by deserialization index
            IXLRow referencedRow = RetrieveTargetRowByIndex(sheet, source.GetReferencedRowIndex());

            // second try: retrieve row by PK value
            if (referencedRow == null)
            {
                referencedRow = RetrieveTargetRowByIdColumnValue(sheet, source);
            }
            return referencedRow;
        }

        private IXLRow RetrieveTargetRowByIndex(IXLWorksheet sheet, int rowIndex)
        {
            if (rowIndex != -1)
            {
                return sheet.Row(rowIndex);
            }
            return null;
        }

        private IXLRow RetrieveTargetRowByIdColumnValue(IXLWorksheet sheet, DataRow source)
        {
            object firstPk = source.TryGetFirstPrimaryKey<object>(null);

            if (firstPk != null
                && source.Table.HasPrimaryKey()
                && source.Table.Columns.Contains(_idColumnName)
                && source.Table.Columns[_idColumnName].HasCellRef())
            {
                return sheet.SearchForXLRow(
                    source.Table.Columns[_idColumnName].GetCellRef().ColumnName,
                    ObjectUtil.ConvertToString(firstPk));
            }
            return null;
        }

        #endregion

        #region Events
        //--------------------------------------------------------------------
        // Events
        //--------------------------------------------------------------------

        #endregion
    }
}